﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Collections.ObjectModel;
using PM.Entity;
using PM.Common;
using System.Data.OracleClient;
using System.Data;

namespace PM.Services
{
    public class TradeServices
    {

        private static Trade GetTradeFromDataReader(IDataReader dataReader)
        {
            Trade retVal = null;
            if (dataReader != null && !dataReader.IsClosed)
            {
                int intTemp = 0;
                double dblTemp = 0;
                retVal = new Trade();

                retVal.TradeDate = dataReader["TRADE_DATE"].ToString();
                retVal.TradeTime = dataReader["TRADE_TIME"].ToString();
                retVal.BuySell = dataReader["BUY_SELL"].ToString();
                retVal.ExecutionId = dataReader["EXECUTION_ID"].ToString();
                retVal.CompanyId = dataReader["COMPANY_ID"].ToString();
                retVal.MainType = dataReader["MAIN_TYPE"].ToString();
                retVal.SubType = dataReader["SUB_TYPE"].ToString();

                intTemp = 0;
                if (int.TryParse(dataReader["QUANTITY"].ToString(), out intTemp))
                    retVal.Quantity = intTemp;

                dblTemp = 0;
                if (double.TryParse(dataReader["PRICE"].ToString(), out dblTemp))
                    retVal.Price = dblTemp;

                retVal.LotType = dataReader["LOT_TYPE"].ToString();

                intTemp = 0;
                if (int.TryParse(dataReader["TAX_FLAG"].ToString(), out intTemp))
                    retVal.TaxFlag = intTemp;

                retVal.BrokerIdBuy = dataReader["BROKER_ID_BUY"].ToString();
                retVal.BrokerIdSell = dataReader["BROKER_ID_SELL"].ToString();

                intTemp = 0;
                if (int.TryParse(dataReader["BUYING_CONTRACT_NO"].ToString(), out intTemp))
                    retVal.BuyingContractNo = intTemp;
                intTemp = 0;
                if (int.TryParse(dataReader["SELLING_CONTRACT_NO"].ToString(), out intTemp))
                    retVal.SellingContractNo = intTemp;
                intTemp = 0;
                if (int.TryParse(dataReader["CLIENT_PREFIX"].ToString(), out intTemp))
                    retVal.ClientPrefix = intTemp;

                retVal.ClientSuffix = dataReader["CLIENT_SUFFIX"].ToString();

                intTemp = 0;
                if (int.TryParse(dataReader["JOINT_AC_NO"].ToString(), out intTemp))
                    retVal.JointAcNo = intTemp;

                retVal.ParticipantId = dataReader["PARTICIPANT_ID"].ToString();

                intTemp = 0;
                if (int.TryParse(dataReader["FOREIGN_FLAG"].ToString(), out intTemp))
                    retVal.ForeignFlag = intTemp;

                dblTemp = 0;
                if (double.TryParse(dataReader["BROKERAGE"].ToString(), out dblTemp))
                    retVal.Brokerage = dblTemp;
                dblTemp = 0;
                if (double.TryParse(dataReader["CDS_FEES"].ToString(), out dblTemp))
                    retVal.CdsFees = dblTemp;
                dblTemp = 0;
                if (double.TryParse(dataReader["CSE_FEES"].ToString(), out dblTemp))
                    retVal.CseFees = dblTemp;
                dblTemp = 0;
                if (double.TryParse(dataReader["STAMP_DUTY"].ToString(), out dblTemp))
                    retVal.StampDuty = dblTemp;
                dblTemp = 0;
                if (double.TryParse(dataReader["SEC_CESS"].ToString(), out dblTemp))
                    retVal.SecCess= dblTemp;
                dblTemp = 0;
                if (double.TryParse(dataReader["BTT"].ToString(), out dblTemp))
                    retVal.Btt = dblTemp;
                dblTemp = 0;
                if (double.TryParse(dataReader["INTEREST_ON_DEBT"].ToString(), out dblTemp))
                    retVal.InterestOnDebt = dblTemp;

                retVal.OrderId = dataReader["ORDER_ID"].ToString();
                retVal.TranId = dataReader["TRAN_ID"].ToString();

                intTemp = 0;
                if (int.TryParse(dataReader["STATUS"].ToString(), out intTemp))
                    retVal.Status = intTemp;

                dblTemp = 0;
                if (double.TryParse(dataReader["GOVERNMENT_CESS"].ToString(), out dblTemp))
                    retVal.GovernmentCess = dblTemp;               
            }
            return retVal;
        }

        public static Trade GetTradeById(string tradeDate, string tradeTime, string buySell, string executionId )    
        {
            Trade retVal = null;
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "SELECT * FROM TRADE WHERE TRADE_DATE = '" + tradeDate + "'" +
                    "AND TRADE_TIME = '" + tradeTime + "'" +
                    "AND BUY_SELL = '" + buySell + "'" +
                    "AND EXECUTION_ID = '" + executionId + "'"; 
                cmd.Connection = con;

                using (IDataReader dataReader = cmd.ExecuteReader())
                {
                    if (dataReader.Read())
                    {
                        retVal = GetTradeFromDataReader(dataReader);
                    }
                }
            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
            return retVal;
        }

        public static TradeCollection GetTradeCollection()
        {
            TradeCollection retVal = null;
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "SELECT * FROM TRADE";
                cmd.Connection = con;

                using (IDataReader dataReader = cmd.ExecuteReader())
                {
                    retVal = new TradeCollection();
                    while (dataReader.Read())
                    {
                        Trade item = GetTradeFromDataReader(dataReader);
                        if (item != null)
                            retVal.Add(item);
                    }
                }
            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
            return retVal;
        }


        public static DataTable GetTradeCollectionDataTable()
        {
            DataTable retVal = null;
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "SELECT * FROM TRADE";
                cmd.Connection = con;

                using (IDataReader dataReader = cmd.ExecuteReader())
                {
                    if (dataReader != null)
                    {
                        retVal = new DataTable();
                        retVal.Load(dataReader);
                    }
                }
            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
            return retVal;

        }

        public static void Add(Trade trade)
        {
            OracleConnection con = new OracleConnection();
            try
            {
                con.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "INSERT INTO TRADE" +
                                    "(TRADE_DATE, TRADE_TIME, BUY_SELL, EXECUTION_ID, COMPANY_ID, MAIN_TYPE, SUB_TYPE, QUANTITY, PRICE, LOT_TYPE, TAX_FLAG, BROKER_ID_BUY, BROKER_ID_SELL, BUYING_CONTRACT_NO, SELLING_CONTRACT_NO, CLIENT_PREFIX, CLIENT_SUFFIX, JOINT_AC_NO, PARTICIPANT_ID, FOREIGN_FLAG, BROKERAGE, CDS_FEES, CSE_FEES, STAMP_DUTY, SEC_CESS, BTT, INTEREST_ON_DEBT, ORDER_ID, TRAN_ID, STATUS, GOVERNMENT_CESS )" +
                                    "VALUES(:v1,    :v2,       :v3,        :v4,            :v5,      :v6,       :v7,      :v8,    :v9,  :v10,       :v11,   :v12,               :v13,       : v14,              :v15,                   :v16,         :v17,       :v18,             :v19,           :v20,       :v21,       :v22,   :v23,       :v24,       :v25, :v26,     :v27,           :v28,   :v29,     :v30,   :v31)";
                
                
                cmd.Parameters.Add(":v1", OracleType.VarChar).Value = trade.TradeDate;
                cmd.Parameters.Add(":v2", OracleType.VarChar).Value = trade.TradeTime;
                cmd.Parameters.Add(":v3", OracleType.VarChar).Value = trade.BuySell;
                cmd.Parameters.Add(":v4", OracleType.VarChar).Value = trade.ExecutionId;
                cmd.Parameters.Add(":v5", OracleType.VarChar).Value = trade.CompanyId;
                cmd.Parameters.Add(":v6", OracleType.VarChar).Value = trade.MainType;
                cmd.Parameters.Add(":v7", OracleType.VarChar).Value = trade.SubType;
                cmd.Parameters.Add(":v8", OracleType.Number).Value = trade.Quantity;
                cmd.Parameters.Add(":v9", OracleType.Number).Value = trade.Price;
                cmd.Parameters.Add(":v10", OracleType.VarChar).Value = trade.LotType;
                cmd.Parameters.Add(":v11", OracleType.Number).Value = trade.TaxFlag;
                cmd.Parameters.Add(":v12", OracleType.VarChar).Value = trade.BrokerIdBuy;
                cmd.Parameters.Add(":v13", OracleType.VarChar).Value = trade.BrokerIdSell;
                cmd.Parameters.Add(":v14", OracleType.Number).Value = trade.BuyingContractNo;
                cmd.Parameters.Add(":v15", OracleType.Number).Value = trade.SellingContractNo;
                cmd.Parameters.Add(":v16", OracleType.Number).Value = trade.ClientPrefix;
                cmd.Parameters.Add(":v17", OracleType.VarChar).Value = trade.ClientSuffix;
                cmd.Parameters.Add(":v18", OracleType.Number).Value = trade.JointAcNo;
                cmd.Parameters.Add(":v19", OracleType.VarChar).Value = trade.ParticipantId;
                cmd.Parameters.Add(":v20", OracleType.Number).Value = trade.ForeignFlag;
                cmd.Parameters.Add(":v21", OracleType.Number).Value = trade.Brokerage;
                cmd.Parameters.Add(":v22", OracleType.Number).Value = trade.CdsFees;
                cmd.Parameters.Add(":v23", OracleType.Number).Value = trade.CseFees;
                cmd.Parameters.Add(":v24", OracleType.Number).Value = trade.StampDuty;
                cmd.Parameters.Add(":v25", OracleType.Number).Value = trade.SecCess;
                cmd.Parameters.Add(":v26", OracleType.Number).Value = trade.Btt;
                cmd.Parameters.Add(":v27", OracleType.Number).Value = trade.InterestOnDebt;
                cmd.Parameters.Add(":v28", OracleType.VarChar).Value = trade.OrderId;
                cmd.Parameters.Add(":v29", OracleType.VarChar).Value = trade.TranId;
                cmd.Parameters.Add(":v30", OracleType.Number).Value = trade.Status;
                cmd.Parameters.Add(":v31", OracleType.Number).Value = trade.GovernmentCess;

                cmd.Connection = con;
                cmd.ExecuteNonQuery();

            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
        }

        public static void Add(Trade trade, OracleConnection con, OracleTransaction tran)
        {
            
            try
            {
                //con.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                if (con.State != ConnectionState.Open)
                {
                    con.Open();    
                }
                
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "INSERT INTO TRADE" +
                                    "(TRADE_DATE, TRADE_TIME, BUY_SELL, EXECUTION_ID, COMPANY_ID, MAIN_TYPE, SUB_TYPE, QUANTITY, PRICE, LOT_TYPE, TAX_FLAG, BROKER_ID_BUY, BROKER_ID_SELL, BUYING_CONTRACT_NO, SELLING_CONTRACT_NO, CLIENT_PREFIX, CLIENT_SUFFIX, JOINT_AC_NO, PARTICIPANT_ID, FOREIGN_FLAG, BROKERAGE, CDS_FEES, CSE_FEES, STAMP_DUTY, SEC_CESS, BTT, INTEREST_ON_DEBT, ORDER_ID, TRAN_ID, STATUS, GOVERNMENT_CESS )" +
                                    "VALUES(:v1,    :v2,       :v3,        :v4,            :v5,      :v6,       :v7,      :v8,    :v9,  :v10,       :v11,   :v12,               :v13,       : v14,              :v15,                   :v16,         :v17,       :v18,             :v19,           :v20,       :v21,       :v22,   :v23,       :v24,       :v25, :v26,     :v27,           :v28,   :v29,     :v30,   :v31)";


                cmd.Parameters.Add(":v1", OracleType.VarChar).Value = trade.TradeDate;
                cmd.Parameters.Add(":v2", OracleType.VarChar).Value = trade.TradeTime;
                cmd.Parameters.Add(":v3", OracleType.VarChar).Value = trade.BuySell;
                cmd.Parameters.Add(":v4", OracleType.VarChar).Value = trade.ExecutionId;
                cmd.Parameters.Add(":v5", OracleType.VarChar).Value = trade.CompanyId;
                cmd.Parameters.Add(":v6", OracleType.VarChar).Value = trade.MainType;
                cmd.Parameters.Add(":v7", OracleType.VarChar).Value = trade.SubType;
                cmd.Parameters.Add(":v8", OracleType.Number).Value = trade.Quantity;
                cmd.Parameters.Add(":v9", OracleType.Number).Value = trade.Price;
                cmd.Parameters.Add(":v10", OracleType.VarChar).Value = trade.LotType;
                cmd.Parameters.Add(":v11", OracleType.Number).Value = trade.TaxFlag;
                cmd.Parameters.Add(":v12", OracleType.VarChar).Value = trade.BrokerIdBuy;
                cmd.Parameters.Add(":v13", OracleType.VarChar).Value = trade.BrokerIdSell;
                cmd.Parameters.Add(":v14", OracleType.Number).Value = trade.BuyingContractNo;
                cmd.Parameters.Add(":v15", OracleType.Number).Value = trade.SellingContractNo;
                cmd.Parameters.Add(":v16", OracleType.Number).Value = trade.ClientPrefix;
                cmd.Parameters.Add(":v17", OracleType.VarChar).Value = trade.ClientSuffix;
                cmd.Parameters.Add(":v18", OracleType.Number).Value = trade.JointAcNo;
                cmd.Parameters.Add(":v19", OracleType.VarChar).Value = trade.ParticipantId;
                cmd.Parameters.Add(":v20", OracleType.Number).Value = trade.ForeignFlag;
                cmd.Parameters.Add(":v21", OracleType.Number).Value = trade.Brokerage;
                cmd.Parameters.Add(":v22", OracleType.Number).Value = trade.CdsFees;
                cmd.Parameters.Add(":v23", OracleType.Number).Value = trade.CseFees;
                cmd.Parameters.Add(":v24", OracleType.Number).Value = trade.StampDuty;
                cmd.Parameters.Add(":v25", OracleType.Number).Value = trade.SecCess;
                cmd.Parameters.Add(":v26", OracleType.Number).Value = trade.Btt;
                cmd.Parameters.Add(":v27", OracleType.Number).Value = trade.InterestOnDebt;
                cmd.Parameters.Add(":v28", OracleType.VarChar).Value = trade.OrderId;
                cmd.Parameters.Add(":v29", OracleType.VarChar).Value = trade.TranId;
                cmd.Parameters.Add(":v30", OracleType.Number).Value = trade.Status;
                cmd.Parameters.Add(":v31", OracleType.Number).Value = trade.GovernmentCess;

                cmd.Connection = con;
                cmd.Transaction = tran;
                cmd.ExecuteNonQuery();

            }
            catch (System.Exception exc)
            {
                throw exc;
            }
            finally
            {
                //con.Close();
            }
        }

        public static void Delete(string tradeDate, string tradeTime, string buySell, string executionId)
        {
            OracleConnection con = new OracleConnection();
            try
            {
                con.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "FROM TRADE WHERE TRADE_DATE = '" + tradeDate + "'" +
                    "AND TRADE_TIME = '" + tradeTime + "'" +
                    "AND BUY_SELL = '" + buySell + "'" +
                    "AND EXECUTION_ID = '" + executionId + "'"; 
                cmd.Connection = con;

                cmd.ExecuteNonQuery();
            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
        }

        public static void Update(Trade trade)
        {
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "UPDATE TRADE SET " +
                                  "COMPANY_ID = :v5, " +
                                  "MAIN_TYPE = :v6, " +
                                  "SUB_TYPE = :v7, " +
                                  "QUANTITY = :v8, " +
                                  "PRICE = :v9, " +
                                  "LOT_TYPE = :v10, " +
                                  "TAX_FLAG = :v11, " +
                                  "BROKER_ID_BUY = :v12, " +
                                  "BROKER_ID_SELL = :v13, " +
                                  "BUYING_CONTRACT_NO = :v14, " +
                                  "SELLING_CONTRACT_NO = :v15, " +
                                  "CLIENT_PREFIX = :v16, " +
                                  "CLIENT_SUFFIX = :v17, " +
                                  "JOINT_AC_NO = :v18, " +
                                  "PARTICIPANT_ID = :v19, " +
                                  "FOREIGN_FLAG = :v20, " +                                  
                                  "BROKERAGE = :v21, " +
                                  "CDS_FEES = :v22, " +
                                  "CSE_FEES = :v23, " +
                                  "STAMP_DUTY = :v24, " +
                                  "SEC_CESS = :v25, " +
                                  "BTT = :v26, " +
                                  "INTEREST_ON_DEBT = :v27, " +
                                  "ORDER_ID = :v28, " +
                                  "TRAN_ID = :v29, " +
                                  "STATUS = :v30, " +
                                  "GOVERNMENT_CESS = :v31, " +
                                  "WHERE TRADE_DATE = :v1" +
                                  "AND TRADE_TIME = :v2" +
                                  "AND BUY_SELL = :v3" +
                                  "AND EXECUTION_ID = :v4" ;




                cmd.Parameters.Add(":v1", OracleType.VarChar).Value = trade.TradeDate;
                cmd.Parameters.Add(":v2", OracleType.VarChar).Value = trade.TradeTime;
                cmd.Parameters.Add(":v3", OracleType.VarChar).Value = trade.BuySell;
                cmd.Parameters.Add(":v4", OracleType.VarChar).Value = trade.ExecutionId;
                cmd.Parameters.Add(":v5", OracleType.VarChar).Value = trade.CompanyId;
                cmd.Parameters.Add(":v6", OracleType.DateTime).Value = trade.MainType;
                cmd.Parameters.Add(":v7", OracleType.VarChar).Value = trade.SubType;
                cmd.Parameters.Add(":v8", OracleType.VarChar).Value = trade.Quantity;
                cmd.Parameters.Add(":v9", OracleType.VarChar).Value = trade.Price;
                cmd.Parameters.Add(":v10", OracleType.VarChar).Value = trade.LotType;
                cmd.Parameters.Add(":v11", OracleType.VarChar).Value = trade.TaxFlag;
                cmd.Parameters.Add(":v12", OracleType.VarChar).Value = trade.BrokerIdBuy;
                cmd.Parameters.Add(":v13", OracleType.Number).Value = trade.BrokerIdSell;
                cmd.Parameters.Add(":v14", OracleType.VarChar).Value = trade.BuyingContractNo;
                cmd.Parameters.Add(":v15", OracleType.VarChar).Value = trade.SellingContractNo;
                cmd.Parameters.Add(":v16", OracleType.VarChar).Value = trade.ClientPrefix;
                cmd.Parameters.Add(":v17", OracleType.VarChar).Value = trade.ClientSuffix;
                cmd.Parameters.Add(":v18", OracleType.VarChar).Value = trade.JointAcNo;
                cmd.Parameters.Add(":v19", OracleType.VarChar).Value = trade.ParticipantId;
                cmd.Parameters.Add(":v20", OracleType.VarChar).Value = trade.ForeignFlag;
                cmd.Parameters.Add(":v21", OracleType.VarChar).Value = trade.Brokerage;
                cmd.Parameters.Add(":v22", OracleType.VarChar).Value = trade.CdsFees;
                cmd.Parameters.Add(":v23", OracleType.VarChar).Value = trade.CseFees;
                cmd.Parameters.Add(":v24", OracleType.VarChar).Value = trade.StampDuty;
                cmd.Parameters.Add(":v25", OracleType.VarChar).Value = trade.SecCess;
                cmd.Parameters.Add(":v26", OracleType.VarChar).Value = trade.Btt;
                cmd.Parameters.Add(":v27", OracleType.VarChar).Value = trade.InterestOnDebt;
                cmd.Parameters.Add(":v28", OracleType.VarChar).Value = trade.OrderId;
                cmd.Parameters.Add(":v29", OracleType.VarChar).Value = trade.TranId;
                cmd.Parameters.Add(":v30", OracleType.VarChar).Value = trade.Status;
                cmd.Parameters.Add(":v31", OracleType.VarChar).Value = trade.GovernmentCess;

                cmd.Connection = con;
                cmd.ExecuteNonQuery();

            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
        }
    }
}
